﻿Imports System.Data.SqlClient
Public Class FrmCapNhatSoBoDiaChinh
    Public m_tennhap As String
    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHuyen.DisplayMember = "T_huyen"
        ComboBoxHuyen.ValueMember = "maH"
        ComboBoxHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKho()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKho", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxKho.DisplayMember = "TenKho"
        ComboBoxKho.ValueMember = "maKhoID"
        ComboBoxKho.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKe()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKe", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxKe.DisplayMember = "TenKe"
        ComboBoxKe.ValueMember = "maKeID"
        ComboBoxKe.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDMHienTrang()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMHienTrang", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHienTrang.DisplayMember = "TenHienTrang"
        ComboBoxHienTrang.ValueMember = "MaHienTrang"
        ComboBoxHienTrang.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiTaiLieu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTaiLieu", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxLoaiTaiLieu.DisplayMember = "TenLoaiTaiLieu"
        ComboBoxLoaiTaiLieu.ValueMember = "maLoaiTaiLieuID"
        ComboBoxLoaiTaiLieu.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiSoBo()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMSoBo", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxLoaiSoBo.DisplayMember = "TenSoBo"
        ComboBoxLoaiSoBo.ValueMember = "maSoBo"
        ComboBoxLoaiSoBo.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub CapNhatSoBoDiaChinh()
        Try
            Dim tennhap As String
            tennhap = ModuleTenDangNhap.m_tennhap
            Dim NgayNhap As String
            NgayNhap = String.Format("{0:MM/dd/yyyy}", DateTimePickerNgayNhap.Value)

            Dim Sql As String = ""
            Sql = "Update SoBoDiaChinh Set " &
                " Quyenso = '" & TextBoxQuyenSo.Text & "' " &
                ",NamLap = '" & TextBoxNamThanhLap.Text & "' " &
           ", Ghichu =  N'" & TextBoxGhiChu.Text & "'" &
           ", CoSoPhapLy = N'" & TextBoxCoSoPhapLy.Text & "'" &
           ", MahuyenID = '" & ComboBoxHuyen.SelectedValue.ToString & "' " &
           ", MaXaID = '" & ComboBoxXa.SelectedValue.ToString & "' " &
           ", MaKhoID = '" & ComboBoxKho.SelectedValue.ToString & "' " &
           ", MaKeID = '" & ComboBoxKe.SelectedValue.ToString & "'" &
           ", MaHienTrangID = '" & ComboBoxHienTrang.SelectedValue.ToString & "' " &
           ", MaLoaiTaiLieuID = '" & ComboBoxLoaiTaiLieu.SelectedValue.ToString & "' " &
           ", MaLoaiSoBoID = '" & ComboBoxLoaiSoBo.SelectedValue.ToString & "' " &
           ", NgayNhap = '" &  NgayNhap & "' " &
           ", NgaySua = " & Format(Now.ToOADate() - 2) & " " &
           ", NguoiNhap =  N'" & tennhap & "' " &
           " WHERE Maso = '" & TextBoxMaSo.Text & "' "
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            NapDataGridViewSoBoDiaChinh()
            MsgBox("Cập nhật sổ bộ thành công!")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub NapSoBoDiaChinhVaoTexbox()
        TextBoxMaSo.DataBindings.Clear()
        TextBoxMaSo.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "Maso")
        DateTimePickerNgayNhap.DataBindings.Clear()
        DateTimePickerNgayNhap.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "NgayNhap")
        TextBoxQuyenSo.DataBindings.Clear()
        TextBoxQuyenSo.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "Quyenso")
        TextBoxNamThanhLap.DataBindings.Clear()
        TextBoxNamThanhLap.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "NamLap")
        TextBoxGhiChu.DataBindings.Clear()
        TextBoxGhiChu.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "Ghichu")
        TextBoxCoSoPhapLy.DataBindings.Clear()
        TextBoxCoSoPhapLy.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "Cosophaply")
        ComboBoxHuyen.DataBindings.Clear()
        ComboBoxHuyen.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "T_Huyen")
        ComboBoxXa.DataBindings.Clear()
        ComboBoxXa.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "Tenxa")
        ComboBoxKho.DataBindings.Clear()
        ComboBoxKho.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "tenkho")
        ComboBoxKe.DataBindings.Clear()
        ComboBoxKe.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "Tenke")
        ComboBoxHienTrang.DataBindings.Clear()
        ComboBoxHienTrang.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "TenHienTrang")
        ComboBoxLoaiTaiLieu.DataBindings.Clear()
        ComboBoxLoaiTaiLieu.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "TenloaiTaiLieu")
        ComboBoxLoaiSoBo.DataBindings.Clear()
        ComboBoxLoaiSoBo.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "Tensobo")
    End Sub
    Private Sub NapDataGridViewSoBoDiaChinh()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select " &
           " Maso" &
            ", NgayNhap" &
           ", Quyenso" &
           ", NamLap" &
           ", Ghichu" &
           ", CoSoPhapLy" &
           ", th.T_huyen" &
           ", tx.TenXa" &
           ", k.TenKho" &
           ", Ke.TenKe" &
           ", ht.TenHienTrang" &
           ", tli.TenLoaiTaiLieu" &
           ", sb.tensobo " &
           " From  SoBoDiaChinh dc,  DMHienTrang ht, DMTaiLieu tli, DMKho k, DMKe ke, tenxa tx, tenhuyen th, DMSoBo sb " &
                                " WHERE " &
                                    " dc.MaLoaiTaiLieuID = tli.maloaiTaiLieuID " &
                                    " and dc.makhoid = k.makhoid " &
                                    " and dc.maKeid = ke.makeid " &
                                    " and dc.MaHuyenId = '" & ComboBoxHuyen.SelectedValue.ToString & "' " &
                                    " and dc.mahuyenid = th.maH " &
                                    " and dc.MaXaId = '" & ComboBoxXa.SelectedValue.ToString & "' " &
                                    " and dc.maxaid = tx.maxa " &
                                    " and dc.maLoaiSoboID = sb.masobo " &
                                    " and dc.maHienTrangID = ht.mahienTrang ", ob_cnn)


        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewSoBoDiaChinh.DataSource = tbl
        If (tbl.Rows.Count = 0) Then
            MessageBox.Show("Không tìm thấy thông tin !")
        Else
        End If
        ob_cnn.Close()
    End Sub
    Private Sub FrmNhapSoDiaChinh_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' to mau cac dong trong datagridlietkebando doi mau khac nhau
        DataGridViewSoBoDiaChinh.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewSoBoDiaChinh.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        NapDonViHuyen()
        NapKho()
        NapKe()
        NapDMHienTrang()
        NapLoaiTaiLieu()
        NapLoaiSoBo()
        '  TextBoxMaSo.Text = checkSoThuTu() + 1
        ' ButtonXoa.Enabled = False
    End Sub
    Private Sub ComboBoxHuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxHuyen.SelectedIndexChanged
        ' load ten huyen
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & ComboBoxHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxXa.DisplayMember = "TenXa"
        ComboBoxXa.ValueMember = "maxa"
        ComboBoxXa.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Public Function checkSoThuTu()
        ob_cnn.Open()
        Dim sqlCom As New SqlCommand("select max(maso) from SoBoDiaChinh ", ob_cnn)
        Dim max As Int32 = Convert.ToInt32(sqlCom.ExecuteScalar())
        ob_cnn.Close()
        Return max
    End Function
    Public Function checkMaso(ByVal ID As String) As Integer
        ob_cnn.Open()
        Dim sqlCom As New SqlCommand("select count(maso) from SoBoDiaChinh where MaSo='" & ID & "' ", ob_cnn)
        Dim count As Int32 = Convert.ToInt16(sqlCom.ExecuteScalar())
        ob_cnn.Close()
        Return count
    End Function
    Private Sub DataGridViewSoBoDiaChinh_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewSoBoDiaChinh.CellClick
        NapSoBoDiaChinhVaoTexbox()
    End Sub
    Private Sub DataGridViewSoBoDiaChinh_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewSoBoDiaChinh.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewSoBoDiaChinh.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewSoBoDiaChinh.RowHeadersWidth < Size.Width + 20 Then DataGridViewSoBoDiaChinh.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub

    Private Sub ButtonSua_Click(sender As Object, e As EventArgs) Handles ButtonSua.Click
        If TextBoxQuyenSo.Text = "" Then
            MessageBox.Show("Chưa nhập thứ tự quyển sổ")
            Return
        End If
        If TextBoxNamThanhLap.Text = "" Then
            MessageBox.Show("Chưa nhập năm thành lập sổ ")
            Return
        End If
        If TextBoxCoSoPhapLy.Text = "" Then
            MessageBox.Show("Chưa nhập cơ sở pháp lý thành lập")
            Return
        Else

            CapNhatSoBoDiaChinh()
            '    NapSoBoDiaChinhVaoTexbox()
            ob_cnn.Close()
        End If
    End Sub

    Private Sub ButtonTimKiem_Click(sender As Object, e As EventArgs) Handles ButtonTimKiem.Click
        NapDataGridViewSoBoDiaChinh()
    End Sub
    Private Sub ButtonXoa_Click(sender As Object, e As EventArgs) Handles ButtonXoa.Click
        If TextBoxQuyenSo.Text = "" Then
            MessageBox.Show("Bạn chưa chọn quyển sổ để xóa ")
            Return
        End If
        If MsgBox("Bạn muốn xóa dữ liệu không ? ", MsgBoxStyle.YesNo, "Thông báo ") = MsgBoxResult.Yes Then
            XoaDuLieu()
            NapDataGridViewSoBoDiaChinh()
            TextBoxQuyenSo.Text = ""
            TextBoxNamThanhLap.Text = ""
            TextBoxGhiChu.Text = ""
            TextBoxCoSoPhapLy.Text = ""
            TextBoxMaSo.Text = ""
            TextBoxNamThanhLap.Text = ""
        Else
            Return
        End If
    End Sub

    Private Sub XoaDuLieu()
        If TextBoxQuyenSo.Text = "" Then
            MessageBox.Show(" Chưa có thông tin để xóa !")
            Return
        Else
            Dim sql As String
            sql = "Delete from SoBoDiaChinh where maso= '" & TextBoxMaSo.Text & " ' "
            Dim cmd As New SqlCommand(sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            MessageBox.Show(" Bạn đã xóa thành công !")
        End If
    End Sub
End Class